package com.example.work.check;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Excel文件导入工具类
 * 支持读取Excel文件并转换为指定的实体对象列表
 */
public class ExcelImportUtil {

    private static final SimpleDateFormat DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    /**
     * 从Excel文件导入数据到指定的实体类列表
     *
     * @param filePath    Excel文件路径
     * @param entityClass 目标实体类
     * @param <T>         实体类型
     * @return 实体对象列表
     * @throws Exception 导入过程中的异常
     */
    public static <T> List<T> importFromExcel(String filePath, Class<T> entityClass) throws Exception {
        List<T> resultList = new ArrayList<>();

        try (FileInputStream fis = new FileInputStream(filePath)) {
            Workbook workbook = createWorkbook(filePath, fis);
            Sheet sheet = workbook.getSheetAt(1); // 读取第一个工作表

            if (sheet.getPhysicalNumberOfRows() <= 1) {
                System.out.println("Excel文件为空或只有标题行");
                return resultList;
            }

            // 获取标题行（第一行）
            Row headerRow = sheet.getRow(0);
            if (headerRow == null) {
                throw new Exception("无法读取Excel标题行");
            }

            // 建立列索引到字段的映射
            Map<Integer, Field> columnFieldMap = buildColumnFieldMap(headerRow, entityClass);

            // 读取数据行
            for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                Row dataRow = sheet.getRow(rowIndex);
                if (dataRow == null || isEmptyRow(dataRow)) {
                    continue;
                }

                T entity = createEntityFromRow(dataRow, entityClass, columnFieldMap);
                if (entity != null) {
                    resultList.add(entity);
                }
            }

            workbook.close();
        }

        return resultList;
    }

    /**
     * 根据文件扩展名创建相应的Workbook对象
     */
    private static Workbook createWorkbook(String filePath, FileInputStream fis) throws IOException {
        if (filePath.endsWith(".xlsx")) {
            return new XSSFWorkbook(fis);
        } else if (filePath.endsWith(".xls")) {
            return new HSSFWorkbook(fis);
        } else {
            throw new IllegalArgumentException("不支持的文件格式，请使用.xls或.xlsx文件");
        }
    }

    /**
     * 建立Excel列索引到实体字段的映射关系
     */
    private static Map<Integer, Field> buildColumnFieldMap(Row headerRow, Class<?> entityClass) {
        Map<Integer, Field> columnFieldMap = new HashMap<>();
        Map<String, Field> fieldMap = getFieldMap(entityClass);

        for (Cell cell : headerRow) {
            if (cell == null) continue;

            String headerName = getCellValueAsString(cell).trim();
            Field field = findFieldByComment(headerName, fieldMap);

            if (field != null) {
                columnFieldMap.put(cell.getColumnIndex(), field);
                System.out.println("映射: 列[" + cell.getColumnIndex() + "] '" + headerName + "' -> 字段 '" + field.getName() + "'");
            } else {
                System.out.println("警告: 无法为列 '" + headerName + "' 找到对应的字段");
            }
        }

        return columnFieldMap;
    }

    /**
     * 获取类的所有字段映射（字段名 -> 字段对象）
     */
    private static Map<String, Field> getFieldMap(Class<?> clazz) {
        Map<String, Field> fieldMap = new HashMap<>();
        Field[] fields = clazz.getDeclaredFields();

        for (Field field : fields) {
            field.setAccessible(true);
            fieldMap.put(field.getName(), field);
        }

        return fieldMap;
    }

    /**
     * 根据Excel标题（字段注释）找到对应的字段
     * 这里需要根据实际的注释规则来实现，暂时使用简单的名称匹配
     */
    private static Field findFieldByComment(String headerName, Map<String, Field> fieldMap) {
        // 这里应该根据实际的字段注释来匹配
        // 由于无法直接获取字段注释，这里提供一个基本的映射示例
        Map<String, String> headerToFieldMap = createHeaderToFieldMapping();

        String fieldName = headerToFieldMap.get(headerName);
        if (fieldName != null) {
            return fieldMap.get(fieldName);
        }

        // 如果没有找到映射，尝试直接匹配字段名
        return fieldMap.get(headerName);
    }



    /**
     * 创建Excel标题到字段名的映射
     * 这里需要根据实际的字段注释来完善
     */
    public static Map<String, String> createHeaderToFieldMapping() {
        Map<String, String> mapping = new HashMap<>();
        getComparisonMapping(mapping);

//        getAssFeilMapping(mapping);
        return mapping;
    }

    private static void getComparisonMapping(Map<String, String> mapping) {


        // 基础字段映射
        mapping.put("div_id", "divId");
        mapping.put("division_asset_id", "divisionAssetId");
        mapping.put("isp_code", "ispCode");
        mapping.put("org_code", "orgCode");
        mapping.put("is_access", "isAccess");
        mapping.put("net_position", "netPosition");
        mapping.put("asset_name", "assetName");
        mapping.put("div_asset_type", "divAssetType");
        mapping.put("div_report_asset_type", "divReportAssetType");
        mapping.put("status", "status");
        mapping.put("location", "location");
        mapping.put("asset_tag", "assetTag");
        mapping.put("asset_model", "assetModel");
        mapping.put("asset_version", "assetVersion");
        mapping.put("div_manufacturer", "divManufacturer");
        mapping.put("div_brand_name", "divBrandName");
        mapping.put("div_brand_region", "divBrandRegion");
        mapping.put("div_open_source", "divOpenSource");
        mapping.put("obeject_name", "objectName");
        mapping.put("obeject_level", "objectLevel");
        mapping.put("found_type", "foundType");
        mapping.put("found_time", "foundTime");
        mapping.put("ports", "ports");
        mapping.put("asset_ip", "assetIp");
        mapping.put("台账2系统名称", "ledger2SystemName");
        mapping.put("台账2ip_address", "ledger2IpAddress");
        mapping.put("台账2port", "ledger2Port");
        mapping.put("台账2删除系统名称", "ledger2DeletedSystemName");
        mapping.put("台账2删除ip_address", "ledger2DeletedIpAddress");
        mapping.put("台账2删除port", "ledger2DeletedPort");
        mapping.put("备注", "remark"); // 中文转英文


        mapping.put("memory_manufacturer", "memoryManufacturer");
        mapping.put("memory_model", "memoryModel");
        mapping.put("chip_manufacturer", "chipManufacturer");
        mapping.put("chip_model", "chipModel");
        mapping.put("cpu_manufacturer", "cpuManufacturer");
        mapping.put("cpu_model", "cpuModel");






        mapping.put("id", "id");
        mapping.put("rela_id", "relaId");
        mapping.put("asset_id", "assetId");
        mapping.put("asset_type", "assetType");
        mapping.put("report_asset_type", "reportAssetType");
        mapping.put("so_state", "soState");
        mapping.put("open_source", "openSource");
        mapping.put("component_name", "componentName");
        mapping.put("component_name_version", "componentNameVersion");
        mapping.put("ip_servce", "ipServce");
        mapping.put("manufacturer", "manufacturer");
        mapping.put("brand_name", "brandName");
        mapping.put("brand_region", "brandRegion");
        mapping.put("ip_access_control", "ipAccessControl");
        mapping.put("ip_access_control_remark", "ipAccessControlRemark");
        mapping.put("software_name", "softwareName");
        mapping.put("software_version", "softwareVersion");
        mapping.put("opera_system_version", "operaSystemVersion");
        mapping.put("hw_model", "hwModel");
        mapping.put("hw_version", "hwVersion");
        mapping.put("found_type_earliest", "foundTypeEarliest");
        mapping.put("update_time", "updateTime");
        mapping.put("record_create_time", "recordCreateTime");
        mapping.put("update_user_name", "updateUserName");
        mapping.put("update_user", "updateUser");
        mapping.put("create_user_name", "createUserName");
        mapping.put("create_user", "createUser");
        mapping.put("create_time", "createTime");
        mapping.put("asset_deleted", "assetDeleted");

        // 基础字段映射
        mapping.put("部侧资产编码", "departmentAssetCode");
        // Web特有字段
        mapping.put("URL", "url");
        mapping.put("协议", "protocol");
        mapping.put("是否采用开源框架", "isOpenSourceFramework");


        mapping.put("文件名", "fileName");
        mapping.put("log中的行号", "logLineNumber");
        mapping.put("指令ID", "instructionId");
        mapping.put("运营商", "operator");
        mapping.put("省份", "province");
        mapping.put("资产ID", "assetId");
        mapping.put("资产IP信息", "assetIpInfo");
        mapping.put("上报端口个数", "reportPortCount");
        mapping.put("资产名称", "assetName");
        mapping.put("全部报送资产类型", "allReportAssetType");
        mapping.put("所属业务系统名称", "businessSystemName");
        mapping.put("定级对象名称", "gradingObjectName");


    }

    private static void getAssFeilMapping(Map<String, String> mapping) {
        // 基础字段映射
        mapping.put("资产编号", "assetNumber");
        mapping.put("资产名称", "assetName");
        mapping.put("是否为自有资产", "selfOwnedAsset");
        mapping.put("是否接入4A", "access4A");
        mapping.put("是否可从互联网访问", "expose");
        mapping.put("IP地址", "assetIp");
        mapping.put("内网IP地址", "intranetIpAddress");
        mapping.put("外网IP地址", "extranetIpAddress");
        mapping.put("内网IPv6地址", "intranetIpv6Address");
        mapping.put("外网IPv6地址", "extranetIpv6Address");
        mapping.put("其他关联IP", "otherRelatedIp");
        mapping.put("资产状态", "assetStatus");
        mapping.put("机柜位置", "cabinetPosition");
        mapping.put("网络位置", "networkLocation");
        mapping.put("报送资产类型", "reportAssetType");
        mapping.put("生产厂商", "manufacturer");
        mapping.put("品牌名称", "brandName");
        mapping.put("品牌区域", "brandRegion");
        mapping.put("界面语言", "interfaceLanguage");
        mapping.put("运行的硬件环境", "runningHardwareEnvironment");
        mapping.put("运行的软件环境", "runningSoftwareEnvironment");
        mapping.put("资产标签", "assetTag");
        mapping.put("所在城市", "city");
        mapping.put("管理线条", "managementLine");
        mapping.put("所属部门", "department");
        mapping.put("所属业务系统", "businessSystem");
        mapping.put("所属安全域", "securityDomain");
        mapping.put("责任人A", "responsiblePersonA");
        mapping.put("责任人A联系方式", "responsiblePersonAContact");
        mapping.put("责任人B", "responsiblePersonB");
        mapping.put("责任人B联系方式", "responsiblePersonBContact");
        mapping.put("安全管理员", "securityAdministrator");
        mapping.put("定级等级", "securityLevel");
        mapping.put("定级对象名称", "levelObjectName");
        mapping.put("网络单元类型", "networkUnitType");
        mapping.put("部侧资产编码", "departmentAssetCode");

        // 数据库特有字段
        mapping.put("数据库类型", "databaseType");
        mapping.put("数据库版本", "databaseVersion");
        mapping.put("端口", "port");
        mapping.put("其他IP", "otherIp");

        // 服务器特有字段
        mapping.put("设备名称", "deviceName");
        mapping.put("型号", "model");
        mapping.put("操作系统名称", "operatingSystemName");
        mapping.put("版本号", "versionNumber");
        mapping.put("CPU生产商", "cpuManufacturer");
        mapping.put("CPU型号", "cpuModel");
        mapping.put("内存生产商", "memoryManufacturer");
        mapping.put("内存型号", "memoryModel");
        mapping.put("是否为虚拟机", "isVirtualMachine");
        mapping.put("远程连接方式", "remoteConnectionMethod");
        mapping.put("鉴权系统资源名", "authSystemResourceName");
        mapping.put("端口列表", "portList");
        mapping.put("端口对应的服务", "portCorrespondingService");

        // Web特有字段
        mapping.put("URL", "url");
        mapping.put("协议", "protocol");
        mapping.put("是否采用开源框架", "isOpenSourceFramework");

        // 中间件特有字段
        mapping.put("中间件类型", "middlewareType");
        mapping.put("中间件版本", "middlewareVersion");

        // 负载均衡特有字段
        mapping.put("交换芯片厂商", "switchChipManufacturer");
        mapping.put("交换芯片型号", "switchChipModel");

        // 其他应用软件特有字段
        mapping.put("开放端口", "openPort");
        mapping.put("端口使用协议", "portProtocol");


        //全部文件
/*
        mapping.put("资产发现方式(0-7)", "assetDiscoveryMethod");
        mapping.put("资产发现时间", "assetDiscoveryTime");
        mapping.put("资产ID", "assetId");
        mapping.put("资产状态(0-3)", "assetStatusAll");
*/

        mapping.put("所属部门", "department");
        mapping.put("文件名", "fileName");
        mapping.put("log中的行号", "logLineNumber");
        mapping.put("指令ID", "instructionId");
        mapping.put("运营商", "operator");
        mapping.put("省份", "province");
        mapping.put("资产ID", "assetId");
        mapping.put("资产IP信息", "assetIpInfo");
        mapping.put("上报端口个数", "reportPortCount");
        mapping.put("资产名称", "assetName");
        mapping.put("报送资产类型", "reportAssetType");
        mapping.put("资产标签", "assetTag");
        mapping.put("所属业务系统名称", "businessSystemName");
        mapping.put("定级等级(0-5)", "securityLevel");
        mapping.put("定级对象名称", "gradingObjectName");
        mapping.put("网络单元类型码表值", "networkUnitTypeCode");
        mapping.put("是否可从互联网访问(0-1)", "isAccessibleFromInternet");
        mapping.put("网络位置(0-1)", "networkLocation");
        mapping.put("资产状态(0-3)", "assetStatusAll");
        mapping.put("型号码表值或自定义值", "modelCodeOrCustomValue");
        mapping.put("型号自定义值相似度", "modelCustomValueSimilarity");
        mapping.put("版本号", "version");
        mapping.put("生产厂商码表值或自定义值", "manufacturerCodeOrCustomValue");
        mapping.put("生产厂商自定义值相似度", "manufacturerCustomValueSimilarity");
        mapping.put("品牌名称码表值或自定义值", "brandNameCodeOrCustomValue");
        mapping.put("品牌名称自定义值相似度", "brandNameCustomValueSimilarity");
        mapping.put("品牌区域码表值", "brandRegionCode");
        mapping.put("cpu生产厂商码表值或自定义值", "cpuManufacturerCodeOrCustomValue");
        mapping.put("CPU生产厂商自定义值相似度", "cpuManufacturerCustomValueSimilarity");
        mapping.put("cpu型号码表值或自定义值", "cpuModelCodeOrCustomValue");
        mapping.put("CPU型号自定义值相似度", "cpuModelCustomValueSimilarity");
        mapping.put("内存生产厂商码表值或自定义值", "memoryManufacturerCodeOrCustomValue");
        mapping.put("内存生产厂商自定义值相似度", "memoryManufacturerCustomValueSimilarity");
        mapping.put("内存型号码表值或自定义值", "memoryModelCodeOrCustomValue");
        mapping.put("内存型号自定义值相似度", "memoryModelCustomValueSimilarity");
        mapping.put("交换芯片生产厂商码表值或自定义值", "switchChipManufacturerCodeOrCustomValue");
        mapping.put("交换芯片生产厂商自定义值相似度", "switchChipManufacturerCustomValueSimilarity");
        mapping.put("交换芯片型号码表值或自定义值", "switchChipModelCodeOrCustomValue");
        mapping.put("交换芯片型号自定义值相似度", "switchChipModelCustomValueSimilarity");
        mapping.put("是否采用开源框架码表值", "isOpenSourceFrameworkCode");
        mapping.put("界面语言建议使用码表值", "interfaceLanguageCode");
        mapping.put("运行的软件环境建议使用码表值", "softwareEnvironmentCode");
        mapping.put("运行的硬件环境建议使用码表值", "hardwareEnvironmentCode");
        mapping.put("资产物理位置", "assetPhysicalLocation");
        mapping.put("资产发现方式(0-7)", "assetDiscoveryMethod");
        mapping.put("资产发现时间", "assetDiscoveryTime");
        mapping.put("备注", "remark");
    }


    /**
     * 从数据行创建实体对象
     */
    private static <T> T createEntityFromRow(Row dataRow, Class<T> entityClass, Map<Integer, Field> columnFieldMap) {
        try {
            T entity = entityClass.getDeclaredConstructor().newInstance();

            for (Map.Entry<Integer, Field> entry : columnFieldMap.entrySet()) {
                int columnIndex = entry.getKey();
                Field field = entry.getValue();

                Cell cell = dataRow.getCell(columnIndex);
                if (cell != null) {
                    Object value = getCellValue(cell, field.getType());
                    if (value != null) {
                        field.set(entity, value);
                    }
                }
            }

            return entity;
        } catch (Exception e) {
            System.err.println("创建实体对象失败: " + e.getMessage());
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 获取单元格的值并转换为指定类型
     */
    private static Object getCellValue(Cell cell, Class<?> targetType) {
        if (cell == null) {
            return null;
        }

        String cellValue = getCellValueAsString(cell).trim();
        if (cellValue.isEmpty()) {
            return null;
        }

        try {
            if (targetType == String.class) {
                return cellValue;
            } else if (targetType == Integer.class || targetType == int.class) {
                return Integer.valueOf(cellValue);
            } else if (targetType == Long.class || targetType == long.class) {
                return Long.valueOf(cellValue);
            } else if (targetType == Double.class || targetType == double.class) {
                return Double.valueOf(cellValue);
            } else if (targetType == Boolean.class || targetType == boolean.class) {
                return Boolean.valueOf(cellValue) || "是".equals(cellValue) || "1".equals(cellValue) || "true".equalsIgnoreCase(cellValue);
            } else if (targetType == Date.class) {
                return DATE_FORMAT.parse(cellValue);
            }
        } catch (Exception e) {
            System.err.println("转换单元格值失败: " + cellValue + " -> " + targetType.getSimpleName() + ", 错误: " + e.getMessage());
        }

        return cellValue; // 默认返回字符串
    }

    /**
     * 获取单元格的字符串值
     */
    private static String getCellValueAsString(Cell cell) {
        if (cell == null) {
            return "";
        }

        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return DATE_FORMAT.format(cell.getDateCellValue());
                } else {
                    double numericValue = cell.getNumericCellValue();
                    if (numericValue == Math.floor(numericValue)) {
                        return String.valueOf((long) numericValue);
                    } else {
                        return String.valueOf(numericValue);
                    }
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }

    /**
     * 检查是否为空行
     */
    private static boolean isEmptyRow(Row row) {
        if (row == null) {
            return true;
        }

        for (Cell cell : row) {
            if (cell != null && !getCellValueAsString(cell).trim().isEmpty()) {
                return false;
            }
        }

        return true;
    }
}